


import db from './mysqlHelper.js';
import moment from 'moment';
import schedule from 'node-schedule';
import { DEVICES } from "./publicValue.js";
import memDB from './memDB.js';
import { broadcast } from './ws.js';

// //每天0 点自动执行日报表写入任务  下
// const job = schedule.scheduleJob('0 0 23 * * *', async () => {
//     console.log("定时任务启动");
//     writeUsedDay();

// });

async function writeUsedHour() {
    let sqls = [];
    let now = moment();
    now = now.subtract(1, 'hours');
    let startTime = now.format('YYYY-MM-DD HH:00:00');
    let endTime = now.format('YYYY-MM-DD HH:59:59');
    for (const key of memDB.Devcies.keys()) {
        let device = memDB.getDevice(key);
        let startRecord = await db.findAllNoPage('used', { sn: device.sn, write_time: startTime }, '', '', 1, 0,);
        if (startRecord.length > 0) {
            startRecord[0].total_flow = startRecord[0].end_value;
            startRecord[0].read_time = startRecord[0].end_time;
        } else {
            startRecord = await db.findAllNoPage('history', { sn: device.sn }, 'read_time', 'asc', 1, 0, [{ field: 'read_time', start: startTime, end: endTime }]);
        }
        let endRecord = await db.findAllNoPage('history', { sn: device.sn }, 'read_time', 'desc', 1, 0, [{ field: 'read_time', start: startTime, end: endTime }]);
        if (startRecord.length == 0 || endRecord.length == 0) {
            console.log(device.sn, '缺少历史数据取消用量统计');
            continue;
        }
        else {
            let used = {
                sn: device.sn,
                project_id: device.project_id,
                device_name: device.name,
                write_time: moment().format('YYYY-MM-DD HH:00:00'),
                start_time: startRecord[0].read_time,
                end_time: endRecord[0].read_time,
                start_value: startRecord[0].total_flow,
                end_value: endRecord[0].total_flow,
                total: endRecord[0].total_flow - startRecord[0].total_flow,
                device_type: startRecord[0].device_type
            }
            sqls.push({ sql: `INSERT INTO used SET ?`, param: [used] });
        }

    }
    if (sqls.length > 0) {
        let res = await db.transaction(sqls);
        if (res instanceof Error) {
            console.error('每小时统计数据写入失败', res.message, JSON.stringify(sqls));
            return;
        }
        console.log(`每小时统计数据写入成功`);
    }
}

async function writeUsedDay() {
    let sqls = [];
    let now = moment();
    let endTime = now.format('YYYY-MM-DD 00:00:01');
    now = now.subtract(1, 'days');
    let startTime = now.format('YYYY-MM-DD 00:00:01');

    for (const key of memDB.Devcies.keys()) {
        let device = memDB.getDevice(key);
        let startRecord = await db.findAllNoPage('used', { sn: device.sn }, 'write_time', 'asc', 1, 0, [{ field: 'write_time', start: startTime, end: endTime }]);
        let endRecord = await db.findAllNoPage('used', { sn: device.sn }, 'write_time', 'desc', 1, 0, [{ field: 'write_time', start: startTime, end: endTime }]);
        if (startRecord.length == 0 || endRecord.length == 0) {
            console.log(device.sn, '缺少历史数据取消用量统计');
            continue;
        }
        else {
            let used = {
                sn: device.sn,
                project_id: device.project_id,
                device_name: device.name,
                write_time: now.format('YYYY-MM-DD 00:00:01'),
                start_time: startRecord[0].start_time,
                end_time: endRecord[0].end_time,
                start_value: startRecord[0].start_value,
                end_value: endRecord[0].end_value,
                total: endRecord[0].end_value - startRecord[0].start_value,
                device_type: startRecord[0].device_type
            }
            sqls.push({ sql: `INSERT INTO used_day SET ?`, param: [used] });
        }
    }
    if (sqls.length > 0) {
        let res = await db.transaction(sqls);
        if (res instanceof Error) {
            console.error('日用量表写入失败', res.message, JSON.stringify(sqls));
            return;
        }
        console.log(`日用量表写入成功`);
    }
}
async function writeUsedMonth() {
    let sqls = [];
    let now = moment();
    let endTime = now.format('YYYY-MM-01 00:00:00');
    now = now.subtract(1, 'months');
    let startTime = now.format('YYYY-MM-01 00:00:01');




    for (const key of memDB.Devcies.keys()) {
        let device = memDB.getDevice(key);
        let startRecord = await db.findAllNoPage('used_day', { sn: device.sn }, 'write_time', 'asc', 1, 0, [{ field: 'write_time', start: startTime, end: endTime }]);
        let endRecord = await db.findAllNoPage('used_day', { sn: device.sn }, 'write_time', 'desc', 1, 0, [{ field: 'write_time', start: startTime, end: endTime }]);
        if (startRecord.length == 0 || endRecord.length == 0) {
            console.log(device.sn, '缺少历史数据取消用量统计');
            continue;
        }
        else {
            let used = {
                sn: device.sn,
                device_name: device.name,
                project_id: device.project_id,
                write_time: now.format('YYYY-MM-01 00:00:01'),
                start_time: startRecord[0].start_time,
                end_time: endRecord[0].end_time,
                start_value: startRecord[0].start_value,
                end_value: endRecord[0].end_value,
                total: endRecord[0].end_value - startRecord[0].start_value,
                device_type: startRecord[0].device_type
            }
            sqls.push({ sql: `INSERT INTO used_month SET ?`, param: [used] });
        }
    }
    if (sqls.length > 0) {
        let res = await db.transaction(sqls);
        if (res instanceof Error) {
            console.error('月用量表写入失败', res.message, JSON.stringify(sqls));
            return;
        }
        console.log(`月用量表写入成功`);
    }
}


//每小时执行一次
const job2 = schedule.scheduleJob('0 0 * * * *', async () => {
    console.log("每小时统计数据启动")
    await writeUsedHour();
    await writePipeLossDailyReport();//生成管损日报表

    if (moment().format('HH') == '00') {
        await writeUsedDay();
        await writePipeLossMonthlyReport();//生成管损月报表

    }
    if (moment().format('DD HH') == '01 00') {
        await writeUsedMonth();
        await writePipeLossYearlyReport(); //生成管损年报表
        
    }
});

//每分钟执行一次
const job1 = schedule.scheduleJob('0 * * * * *', async () => {
    console.log("一分钟定时任务启动")

    let now = moment().subtract(5, 'minutes');



    let keys = DEVICES.keys();
    for (const key of keys) {
        //  console.log('设备通讯超时检查', key);
        let device = memDB.getDevice(key);
        if (device == null) {
            continue;
        }
        if (device.last_activivty.isBefore(now)) {
            console.log('设备通讯超时下线', device.sn);
            let oper_device = DEVICES.get(device.sn);
            if (oper_device != null) {
                oper_device.destroy();//释放资源
            }
            //   updateDeviceStatus(device.sn, false);
            //多map中删除设备
            DEVICES.delete(device.sn);
        }
    }
    let sql = `select * from device where is_online=1 and read_time<= '${now.format('YYYY-MM-DD HH:mm:ss')}'`;
    let res = await db.query(sql);
    if (res instanceof Error) {
        console.error('定时查询在线且超时没有通讯的设备出错', res.message);
        return;
    }
    let sqls = [];
    for (const row of res) {
        console.log('设备通讯超时下线', row.sn);
        //生成离线记录
        let history = {
            sn: row.sn, name: row.name, enterprise_id: row.enterprise_id,
            type: row.type, model: row.model, is_online: 0, write_time: moment().format('YYYY-MM-DD HH:mm:ss'),
            is_read: 1, project_id: row.project_id
        };
        sqls.push({ sql: `UPDATE device SET is_online=0 WHERE sn='${row.sn}'` });
        sqls.push({ sql: `INSERT INTO offline_record SET ?`, param: [history] });
    }
    if (sqls.length > 0) {
        let res = await db.transaction(sqls);
        if (res instanceof Error) {
            console.error('设备通讯超时下线失败', res.message, JSON.stringify(sqls));
            return;
        }

    }
    console.log(`设备通讯超时下线成功`);

});




async function updateDeviceStatus(sn, is_online) {
    console.log(`设备${sn}状态更新`, is_online);
    let device = await db.findOne('device', { sn: sn });
    if (device == null) {
        console.log(`设备${sn}不存在`);
        return;
    };
    device.is_online = is_online;
    let res = await db.update('device', { is_online: is_online }, { sn: sn });
    if (res instanceof Error) {
        console.error('设备${sn}状态更新失败', res.message);
        return;
    }
    console.log(`设备${sn}状态更新成功`);
}

async function writeRealData(sn, data) {
    console.log(`设备${sn}实时数据写入`, JSON.stringify(data));

    let device = await db.findOne('device', { sn: sn });
    if (device == null) {
        console.log(`设备${sn}不存在`);
        return;
    };
    let history = { sn: sn, device_name: device.name, device_type: device.type };
    //遍历data对象并更新到数据库
    for (const key in data) {
        if (data[key] != null) {
            device[key] = data[key];
            history[key] = data[key];
        }
    }
    device.is_online = 1;
    let sqls = [];
    //生成插入实时数据的sql语句
    sqls.push({ sql: `UPDATE device SET ? WHERE ?`, param: [device, { sn: sn }] });
    history.is_online = 1;
    sqls.push({ sql: `INSERT INTO history SET ?`, param: [history] });
    //执行插入数据库
    let res = await db.transaction(sqls);
    if (res instanceof Error) {
        console.error('设备${sn}实时数据写入失败', res.message, JSON.stringify(sqls));
        return;
    }
    //广播实时数据
    broadcast(JSON.stringify({ cmd: "data_update", data: device }));
    console.log(`设备${sn}实时数据写入成功`);





}






//自动删除半年以前history数据
const job3 = schedule.scheduleJob('0 0 0 1 * *', async () => {
    console.log("删除半年前数据启动")
    let now = moment().subtract(6, 'months').format('YYYY-MM-DD HH:mm:ss');
    let res = await db.delete('history', { read_time: now });
    if (res instanceof Error) {
        console.error('删除半年前数据失败', res.message);
        return;
    }
    console.log(`删除半年前数据成功`);
});

// async function test(){
//     setTimeout(()=>{
//         writeUsedHour();
//     },2000);
// }

// test();

// 管损日报表生成
async function writePipeLossDailyReport() {
    // 统计上一小时
    const lastHour = moment().subtract(1, 'hours');
    const start = lastHour.format('YYYY-MM-DD HH:00:00');
    const end = lastHour.format('YYYY-MM-DD HH:59:59');
    const write_time = start; // 以上一小时起始时间为write_time

    // 查询总表用量
    const mainSql = `
        SELECT project_id, SUM(total) AS main_total
        FROM used
        WHERE device_type = 0 AND write_time >= ? AND write_time <= ?
        GROUP BY project_id
    `;
    // 查询分表用量
    const subSql = `
        SELECT project_id, SUM(total) AS sub_total
        FROM used
        WHERE device_type = 1 AND write_time >= ? AND write_time <= ?
        GROUP BY project_id
    `;

    const mainRows = await db.query(mainSql, [start, end]);
    const subRows = await db.query(subSql, [start, end]);

    // 合并项目
    const projectMap = new Map();
    for (const row of mainRows) {
        projectMap.set(row.project_id, { main_total: Number(row.main_total) || 0, sub_total: 0 });
    }
    for (const row of subRows) {
        if (!projectMap.has(row.project_id)) {
            projectMap.set(row.project_id, { main_total: 0, sub_total: Number(row.sub_total) || 0 });
        } else {
            projectMap.get(row.project_id).sub_total = Number(row.sub_total) || 0;
        }
    }

    // 生成插入SQL
    const sqls = [];
    for (const [project_id, { main_total, sub_total }] of projectMap.entries()) {
        const loss_total = main_total - sub_total;
        const loss_rate = main_total === 0 ? 0 : Number(((loss_total) / main_total).toFixed(4));
        sqls.push({
            sql: `INSERT INTO pipe_loss_daily_report (project_id, write_time, main_total, sub_total, loss_total, loss_rate) VALUES (?, ?, ?, ?, ?, ?)`,
            param: [project_id, write_time, main_total, sub_total, loss_total, loss_rate]
        });
    }
    if (sqls.length > 0) {
        const res = await db.transaction(sqls);
        if (res instanceof Error) {
            console.error('管损日报表写入失败', res.message, JSON.stringify(sqls));
            return;
        }
        console.log('管损日报表写入成功');
    } else {
        console.log('上一小时无数据，无需写入管损日报表');
    }
}

// 管损月报表生成
async function writePipeLossMonthlyReport() {
    // 统计本月1号到昨天的日报表数据
    const now = moment();
    const yesterday = moment().subtract(1, 'days');
    const monthStart = yesterday.clone().startOf('month').format('YYYY-MM-DD 00:00:00');
    const monthEnd = yesterday.format('YYYY-MM-DD 23:59:59');
    const write_time = monthEnd; // 月报写入时间为昨日23:59:59

    // 汇总本月1号到昨日的日报表
    const sql = `
        SELECT project_id,
               SUM(main_total) AS main_total,
               SUM(sub_total) AS sub_total,
               SUM(loss_total) AS loss_total,
               CASE WHEN SUM(main_total) = 0 THEN 0 ELSE ROUND(SUM(loss_total)/SUM(main_total), 4) END AS loss_rate
        FROM pipe_loss_daily_report
        WHERE write_time >= ? AND write_time <= ?
        GROUP BY project_id
    `;
    const rows = await db.query(sql, [monthStart, monthEnd]);

    // 生成插入SQL
    const sqls = [];
    for (const row of rows) {
        sqls.push({
            sql: `INSERT INTO pipe_loss_monthly_report (project_id, write_time, main_total, sub_total, loss_total, loss_rate) VALUES (?, ?, ?, ?, ?, ?)`,
            param: [row.project_id, write_time, Number(row.main_total) || 0, Number(row.sub_total) || 0, Number(row.loss_total) || 0, Number(row.loss_rate) || 0]
        });
    }
    if (sqls.length > 0) {
        const res = await db.transaction(sqls);
        if (res instanceof Error) {
            console.error('管损月报表写入失败', res.message, JSON.stringify(sqls));
            return;
        }
        console.log('管损月报表写入成功');
    } else {
        console.log('本月无日报数据，无需写入管损月报表');
    }
}

// 管损年报表生成
async function writePipeLossYearlyReport() {
    // 统计上一年1月1日到12月31日的月报数据
    const now = moment();
    const lastYear = now.clone().subtract(1, 'years');
    const yearStart = lastYear.clone().startOf('year').format('YYYY-01-01 00:00:00');
    const yearEnd = lastYear.clone().endOf('year').format('YYYY-12-31 23:59:59');
    const write_time = yearEnd; // 年报写入时间为上一年12月31日23:59:59

    // 汇总上一年所有月报
    const sql = `
        SELECT project_id,
               SUM(main_total) AS main_total,
               SUM(sub_total) AS sub_total,
               SUM(loss_total) AS loss_total,
               CASE WHEN SUM(main_total) = 0 THEN 0 ELSE ROUND(SUM(loss_total)/SUM(main_total), 4) END AS loss_rate
        FROM pipe_loss_monthly_report
        WHERE write_time >= ? AND write_time <= ?
        GROUP BY project_id
    `;
    const rows = await db.query(sql, [yearStart, yearEnd]);

    // 生成插入SQL
    const sqls = [];
    for (const row of rows) {
        sqls.push({
            sql: `INSERT INTO pipe_loss_yearly_report (project_id, write_time, main_total, sub_total, loss_total, loss_rate) VALUES (?, ?, ?, ?, ?, ?)`,
            param: [row.project_id, write_time, Number(row.main_total) || 0, Number(row.sub_total) || 0, Number(row.loss_total) || 0, Number(row.loss_rate) || 0]
        });
    }
    if (sqls.length > 0) {
        const res = await db.transaction(sqls);
        if (res instanceof Error) {
            console.error('管损年报表写入失败', res.message, JSON.stringify(sqls));
            return;
        }
        console.log('管损年报表写入成功');
    } else {
        console.log('去年无月报数据，无需写入管损年报表');
    }
}







export { writeRealData, updateDeviceStatus }